package zry.Achievement;

import zry.Search.SearchCriteriaAchievement;
import zry.Util.DBUtil;
import Connection.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AchievementDAOImpl extends DAOBase implements AchievementDAO {

    private static final String ACHIEVEMENT_INSERT_SQL = "INSERT INTO achievement_table(achievement_type,student_id) VALUES(?,?)";
    private static final String ACHIEVEMENT_UPDATE_SQL = "UPDATE achievement_table SET achievement_type=?,student_id=?,preliminary_review=?,final_review=? WHERE achievement_id=?";
    private static final String ACHIEVEMENT_DELETE_SQL = "DELETE FROM achievement_table WHERE achievement_id=?";
    private static final String ACHIEVEMENT_SELECT_SQL = "SELECT * FROM achievement_table WHERE achievement_id=?";
    private static final String ACHIEVEMENT_SELECT_ALL_SQL = "SELECT * FROM achievement_table";

    @Override
    public String addAchievement(Achievement achievement) {
        Connection con = null;
        String key=null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(ACHIEVEMENT_INSERT_SQL,Statement.RETURN_GENERATED_KEYS);
            psmt.setString(1, DBUtil.fixSqlFieldValue(achievement.getA_type()));
            psmt.setString(2, DBUtil.fixSqlFieldValue(achievement.getS_id()));
            psmt.executeUpdate();
            ResultSet resultSet=psmt.getGeneratedKeys();
            if(resultSet.next())
            {
                key=resultSet.getString(1);
            }
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return key;
    }

    @Override
    public void updateAchievement(Achievement achievement) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(ACHIEVEMENT_UPDATE_SQL);
            psmt.setString(1, DBUtil.fixSqlFieldValue(achievement.getA_type()));
            psmt.setString(2, DBUtil.fixSqlFieldValue(achievement.getS_id()));
            psmt.setInt(3, achievement.getP_review());
            psmt.setInt(4, achievement.getF_review());
            psmt.setString(5, achievement.getA_id());
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void deleteAchievement(String achievement_no) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(ACHIEVEMENT_DELETE_SQL);
            psmt.setString(1, achievement_no);
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public Achievement getAchievement(String achievement_id) {
        Achievement achievement = null;
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(ACHIEVEMENT_SELECT_SQL);
            psmt.setString(1, achievement_id);
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                achievement = new Achievement();
                achievement.setA_id(rs.getString("achievement_id"));
                achievement.setA_type(rs.getString("achievement_type"));
                achievement.setS_id(rs.getString("student_id"));
                achievement.setP_review(rs.getInt("preliminary_review"));
                achievement.setF_review(rs.getInt("final_review"));
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return achievement;
    }

    public List<Achievement> getAllAchievements() {
        List<Achievement> achievements = new ArrayList<>();
        Connection con = null;
        try {
            con = getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(ACHIEVEMENT_SELECT_ALL_SQL);
            while (rs.next()) {
                Achievement achievement = new Achievement();
                achievement.setA_id(rs.getString("achievement_id"));
                achievement.setA_type(rs.getString("achievement_type"));
                achievement.setS_id(rs.getString("student_id"));
                achievement.setP_review(rs.getInt("preliminary_review"));
                achievement.setF_review(rs.getInt("final_review"));
                achievements.add(achievement);
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return achievements;
    }

    @Override
    public List<Achievement> findAchievements(SearchCriteriaAchievement searchCriteriaAchievement) {
        List<Achievement> achievements = new ArrayList<>();
        Connection con = null;
        try {
            // 构建SQL语句
            StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM achievement_table WHERE 1=1");
            if (searchCriteriaAchievement.getA_id()!= null) {
                sqlBuilder.append(" AND achievement_id LIKE ?");
            }
            if (searchCriteriaAchievement.getA_type() != null) {
                sqlBuilder.append(" AND achievement_type LIKE ?");
            }
            if (searchCriteriaAchievement.getS_id() != null) {
                sqlBuilder.append(" AND student_id LIKE ?");
            }
            if (searchCriteriaAchievement.getP_review() != null) {
                sqlBuilder.append(" AND preliminary_review LIKE ?");
            }
            if (searchCriteriaAchievement.getF_review() != null) {
                sqlBuilder.append(" AND final_review LIKE ?");
            }
            String sql = sqlBuilder.toString();

            // 执行查询
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(sql);
            int index = 1;
            if (searchCriteriaAchievement.getA_id() != null) {
                psmt.setString(index++, searchCriteriaAchievement.getA_id());
            }
            if (searchCriteriaAchievement.getA_type() != null) {
                psmt.setString(index++, searchCriteriaAchievement.getA_type());
            }
            if (searchCriteriaAchievement.getS_id() != null) {
                psmt.setString(index++, searchCriteriaAchievement.getS_id());
            }
            if (searchCriteriaAchievement.getP_review() != null) {
                psmt.setInt(index++, searchCriteriaAchievement.getP_review());
            }
            if (searchCriteriaAchievement.getF_review() != null) {
                psmt.setInt(index++, searchCriteriaAchievement.getF_review());
            }
            ResultSet rs = psmt.executeQuery();
            while (rs.next()) {
                String a_id = rs.getString("achievement_id");
                String a_type = rs.getString("achievement_type");
                String s_id= rs.getString("student_id");
                Integer p_review = rs.getInt("preliminary_review");
                Integer f_review = rs.getInt("final_review");
                Achievement achievement = new Achievement(a_id, a_type, s_id, p_review, f_review);
                achievements.add(achievement);
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return achievements;
    }
}